package control;

import view.Main;
import model.Distance;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Mikkel
 */
public class DistanceHandler {

    private final static String TABEL = "Distance";
    private SQLCommunication sqlc;
    private ArrayList<Distance> distanceList;

    public DistanceHandler(Main main) {
        this.sqlc = main.getSql();
        distanceList = new ArrayList<>();
    }

    public ArrayList<Distance> instantierDistanceList() {
        distanceList.clear();
        int ID;
        String startDestination;
        String endDestination;
        int distance;

        String sql = "SELECT * FROM " + TABEL;
        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            while (rs.next()) {
                ID = rs.getInt("ID");
                distance = rs.getInt("Distance");
                startDestination = rs.getString("StartDestination");
                endDestination = rs.getString("EndDestination");
                Distance distance1 = new Distance(ID, startDestination, endDestination, distance);
                distanceList.add(distance1);
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af DistanceList");
        }
        return distanceList;
    }

    public ArrayList<String> getAllCities() {
        ArrayList<String> cityList = new ArrayList<>();
        String startDestination;

        String sql = "Select * from distance group by distance.StartDestination";
        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            while (rs.next()) {
                startDestination = rs.getString("StartDestination");
                cityList.add(startDestination);
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af DistanceList");
        }
        
        return cityList;
    }

    public Distance getDistanceByID(int ID) {
        Distance distance = null;
        String sql;
        sql = "SELECT * FROM " + TABEL + " where ID = " + ID;

        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            if (rs.next()) {
                distance = new Distance(rs.getInt("ID"), rs.getString("StartDestination"), rs.getString("EndDestination"), rs.getInt("Distance"));
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved hentning af Distance");
        }
        return distance;
    }

    public Distance getDistanceByCities(String pickUp, String delivery) {
        Distance distance = null;
        String sql;
        sql = "SELECT * FROM " + TABEL + " where StartDestination = '" + pickUp + "' "
                + "AND EndDestination = '" + delivery + "'";

        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            if (rs.next()) {
                distance = new Distance(rs.getInt("ID"), rs.getString("StartDestination"), rs.getString("EndDestination"), rs.getInt("Distance"));
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af DistanceList");
        }
        return distance;
    }

    public boolean checkDistanceByCities(String pickUp, String delivery) {
        boolean exists = false;
        String sql;
        sql = "SELECT * FROM " + TABEL + " where StartDestination = '" + pickUp + "' "
                + "AND EndDestination = '" + delivery + "'";

        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            if (rs.next()) {
                exists = true;
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af DistanceList");
        }
        return exists;
    }
    
    public String getZipcodeByCity(String city){
        String zipcode="Fejl!";
        String sql;
        sql = "SELECT Zipcode FROM City WHERE City = '"+city+"'";
        ResultSet rs = sqlc.select("City", sql);

        try {
            if(rs.next()) {
              zipcode = rs.getString("Zipcode");
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved hentning af zipcode\n" + sql );
        }
        return zipcode;
    }
    
    public String getCountryByCity (String city){
        String country = "Fejl!";
        String sql;
        sql = "SELECT Country FROM City WHERE City = '"+city+"'";
        ResultSet rs = sqlc.select("City", sql);

        try {
            if(rs.next()) {
              country = rs.getString("Country");
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved hentning af Country\n" + sql );
        }
        return country;
    }
}